package com.gzhryc.jdbc.dialect.mysql;

import com.gzhryc.jdbc.Query;
import com.gzhryc.jdbc.dialect.AbsJdbcHelper;
import com.gzhryc.jdbc.log.Logger;
import com.gzhryc.jdbc.models.Parameters;
import com.gzhryc.jdbc.utils.ResultSetUtils;

import java.sql.*;
import java.util.List;
import java.util.Map;

public class MysqlJdbcHelper extends AbsJdbcHelper {

	static Logger log = Logger.getLogger(MysqlJdbcHelper.class);

	public MysqlJdbcHelper(String connectionKey) {
		super(connectionKey);
	}

	public <T> T get(Query query, Class<T> clazz) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			String sql = query.getSql() + " LIMIT 1"; // SQL优化
			PreparedStatement pstmt = conn.prepareStatement(sql);
			query.getParams().toPreparedStatement(pstmt);
			log.sql(connectionKey,sql,query.getParams());
			return ResultSetUtils.read(pstmt.executeQuery(), clazz);
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	public <T> T getJoin(Query query, Class<T> clazz) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			String sql = query.getSql() + " LIMIT 1"; // SQL优化
			PreparedStatement pstmt = conn.prepareStatement(sql);
			query.getParams().toPreparedStatement(pstmt);
			log.sql(connectionKey,sql,query.getParams());
			return ResultSetUtils.readJoin(pstmt.executeQuery(), clazz);
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	public Map<String, Object> get(Query query) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			String sql = query.getSql() + " LIMIT 1"; // SQL优化
			PreparedStatement pstmt = conn.prepareStatement(sql);
			query.getParams().toPreparedStatement(pstmt);
			log.sql(connectionKey,sql,query.getParams());
			return ResultSetUtils.read(pstmt.executeQuery());
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	public <T> List<T> find(String sql, Class<T> clazz, Integer startNum, Integer pageLimit) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			sql = sql + " LIMIT " + startNum + "," + pageLimit;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			log.sql(connectionKey,sql,null);
			return ResultSetUtils.readList(pstmt.executeQuery(), clazz);
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	public <T> List<T> find(Query query, Class<T> clazz) throws SQLException {
		Connection conn = null;
		conn = getConn();
		String sql = query.getSql();
		if (query.isPaging()) {
			sql = sql + " LIMIT " + query.getStartNum() + "," + query.getPageLimit();
		}
		PreparedStatement pstmt = conn.prepareStatement(sql);
		query.getParams().toPreparedStatement(pstmt);
		log.sql(connectionKey,sql,query.getParams());
		try {
			return ResultSetUtils.readList(pstmt.executeQuery(), clazz);
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	public List<Map<String, Object>> find(Query query) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			String sql = query.getSql();
			if (query.isPaging()) {
				sql = sql + " LIMIT " + query.getStartNum() + "," + query.getPageLimit();
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			query.getParams().toPreparedStatement(pstmt);
			log.sql(connectionKey,sql,query.getParams());
			return ResultSetUtils.readList(pstmt.executeQuery());
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	public List<Map<String, Object>> find(String sql, Integer startNum, Integer pageLimit) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			sql = sql + " LIMIT " + startNum + "," + pageLimit;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			log.sql(connectionKey,sql,null);
			return ResultSetUtils.readList(pstmt.executeQuery());
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	public int insert(String sql, Parameters params) throws SQLException {
		Connection conn = null;
		conn = getConn();
		// 获取自增长序列
		PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
		try {
			params.toPreparedStatement(pstmt);
			log.sql(connectionKey,sql,params);
			return pstmt.executeUpdate();
		} finally {
			if (pstmt != null) {
				pstmt.close();
			}
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	@Override
	public Long insertByAutoId(String sql, Parameters params) throws SQLException {
		Connection conn = null;
		Long id = null;
		try {
			conn = getConn();
			// 获取自增长序列
			PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			params.toPreparedStatement(pstmt);
			log.sql(connectionKey,sql,params);
			int i = pstmt.executeUpdate();
			if (i > 0) {
				ResultSet rs = pstmt.getGeneratedKeys();
				if (rs.next()) {
					id = rs.getLong(1);
				}
				rs.close();
			}
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
		return id;
	}

	@Override
	public <T> List<T> findJoin(String sql, Class<T> clazz, Integer startNum, Integer pageLimit) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			sql = sql + " LIMIT " + startNum + "," + pageLimit;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			log.sql(connectionKey,sql,null);
			return ResultSetUtils.readJoinList(pstmt.executeQuery(), clazz);
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

	@Override
	public <T> List<T> findJoin(Query query, Class<T> clazz) throws SQLException {
		Connection conn = null;
		try {
			conn = getConn();
			String sql = query.getSql();
			if (query.isPaging()) {
				sql = sql + " LIMIT " + query.getStartNum() + "," + query.getPageLimit();
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			query.getParams().toPreparedStatement(pstmt);
			log.sql(connectionKey,sql,query.getParams());
			return ResultSetUtils.readJoinList(pstmt.executeQuery(), clazz);
		} finally {
			// 如果事自动事务则必需关闭链接
			if (conn != null && conn.getAutoCommit()) {
				conn.close();
			}
		}
	}

}
